﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Windows.Forms;

namespace Report
{
    class SQL
    {
         SqlConnection conn;
         string strConnect = "server=172.16.5.194;user id=sa;Password=hafic@2012;database=DWH_REPORT ;Connection Timeout=1000 ;Max Pool Size=1000;Min Pool Size=5";
         SqlDataAdapter adapter;
         public SQL()
        {
            conn = new SqlConnection(strConnect);
        }
         public void execute_Query(string strStoredProcedures, SqlParameter[] sqlParameters)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                foreach (SqlParameter sqlPara in sqlParameters)
                {
                    cmd.Parameters.Add(sqlPara);
                }
                cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                this.close();
            }
        }

         public SqlConnection open()
        {
            try
            {
                conn.Open();
                return conn;
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error..", ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
        }
         public void close()
        {
            try
            {
                conn.Close();
            }
            catch (SqlException ex)
            {
                MessageBox.Show("Error...", ex.Message, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }


         public void setParameter(string value, string name, rptUyNhiemChi cr)
        {
            ParameterFieldDefinitions crParameterFieldDefinitions;
            ParameterFieldDefinition crParameterFieldDefinition;
            ParameterValues crParameterValues = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
            crParameterDiscreteValue.Value = value.Trim();
            crParameterFieldDefinitions = cr.DataDefinition.ParameterFields;
            crParameterFieldDefinition = crParameterFieldDefinitions[name];
            crParameterValues = crParameterFieldDefinition.CurrentValues;
            crParameterValues.Clear();
            crParameterValues.Add(crParameterDiscreteValue);
            crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);


        }
         public void setParameter(double value, string name, rptUyNhiemChi cr)
        {
            ParameterFieldDefinitions crParameterFieldDefinitions;
            ParameterFieldDefinition crParameterFieldDefinition;
            ParameterValues crParameterValues = new ParameterValues();
            ParameterDiscreteValue crParameterDiscreteValue = new ParameterDiscreteValue();
            crParameterDiscreteValue.Value = value;
            crParameterFieldDefinitions = cr.DataDefinition.ParameterFields;
            crParameterFieldDefinition = crParameterFieldDefinitions[name];
            crParameterValues = crParameterFieldDefinition.CurrentValues;
            crParameterValues.Clear();
            crParameterValues.Add(crParameterDiscreteValue);
            crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);


        }
         public DataTable getDataTable(string strStoredProcedures, SqlParameter[] sqlParameters) 
        {
            try 
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = this.open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = strStoredProcedures;

                foreach (SqlParameter sqlPara in sqlParameters) 
                {
                    cmd.Parameters.Add(sqlPara);
                }
                cmd.ExecuteNonQuery();
                DataTable dt = new DataTable();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(dt);
                return dt;
            }
            catch (SqlException ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return null;
            }
            finally
            {
                this.close();
            }
        }

         public void execute_Query(string strStoredProcedures, SqlParameter sqlPara)
         {
             try
             {
                 SqlCommand cmd = new SqlCommand();

                 cmd.Connection = this.open();
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.CommandText = strStoredProcedures;

                 cmd.Parameters.Add(sqlPara);
                 cmd.ExecuteNonQuery();

             }
             catch (SqlException ex)
             {
                 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
             }
             finally
             {
                 this.close();
             }
         }
         public string ChuyenSo(string number)
         {
             string[] dv = { "", "mươi", "trăm", "nghìn", "triệu", "tỉ" };
             string[] cs = { "không", "một", "hai", "ba", "bốn", "năm", "sáu", "bảy", "tám", "chín" };
             string doc;
             int i, j, k, n, len, found, ddv, rd;

             len = number.Length;
             number += "ss";
             doc = "";
             found = 0;
             ddv = 0;
             rd = 0;

             i = 0;
             while (i < len)
             {
                 //So chu so o hang dang duyet
                 n = (len - i + 2) % 3 + 1;

                 //Kiem tra so 0
                 found = 0;
                 for (j = 0; j < n; j++)
                 {
                     if (number[i + j] != '0')
                     {
                         found = 1;
                         break;
                     }
                 }

                 //Duyet n chu so
                 if (found == 1)
                 {
                     rd = 1;
                     for (j = 0; j < n; j++)
                     {
                         ddv = 1;
                         switch (number[i + j])
                         {
                             case '0':
                                 if (n - j == 3) doc += cs[0] + " ";
                                 if (n - j == 2)
                                 {
                                     if (number[i + j + 1] != '0') doc += "lẻ ";
                                     ddv = 0;
                                 }
                                 break;
                             case '1':
                                 if (n - j == 3) doc += cs[1] + " ";
                                 if (n - j == 2)
                                 {
                                     doc += "mười ";
                                     ddv = 0;
                                 }
                                 if (n - j == 1)
                                 {
                                     if (i + j == 0) k = 0;
                                     else k = i + j - 1;

                                     if (number[k] != '1' && number[k] != '0')
                                         doc += "mốt ";
                                     else
                                         doc += cs[1] + " ";
                                 }
                                 break;
                             case '5':
                                 if (i + j == len - 1)
                                     doc += "lăm ";
                                 else
                                     doc += cs[5] + " ";
                                 break;
                             default:
                                 doc += cs[(int)number[i + j] - 48] + " ";
                                 break;
                         }

                         //Doc don vi nho
                         if (ddv == 1)
                         {
                             doc += dv[n - j - 1] + " ";
                         }
                     }
                 }


                 //Doc don vi lon
                 if (len - i - n > 0)
                 {
                     if ((len - i - n) % 9 == 0)
                     {
                         if (rd == 1)
                             for (k = 0; k < (len - i - n) / 9; k++)
                                 doc += "tỉ ";
                         rd = 0;
                     }
                     else
                         if (found != 0) doc += dv[((len - i - n + 1) % 9) / 3 + 2] + " ";
                 }

                 i += n;
             }

             if (len == 1)
                 if (number[0] == '0' || number[0] == '5') return cs[(int)number[0] - 48];

             return doc;
         }
         public DataTable getDataTable(string strStoredProcedures, SqlParameter sqlPara)
         {
             try
             {
                 SqlCommand cmd = new SqlCommand();

                 cmd.Connection = this.open();
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.CommandText = strStoredProcedures;

                 cmd.Parameters.Add(sqlPara);

                 cmd.ExecuteNonQuery();
                 DataTable dt = new DataTable();
                 adapter = new SqlDataAdapter(cmd);
                 adapter.Fill(dt);
                 return dt;
             }
             catch
             {
                 return null;
             }
             finally
             {
                 this.close();
             }
         }

    }

}
